package cn.itcast.goods.book.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.OrderBy;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.bookstore.constants.PageConstants;
import cn.itcast.commons.CommonUtils;
import cn.itcast.goods.book.domain.Book;
import cn.itcast.goods.category.doamin.Category;
import cn.itcast.goods.pager.PageBean;
import cn.itcast.jdbc.TxQueryRunner;

public class BookDao {

	private QueryRunner qr=new TxQueryRunner();
	
	/**
	 * 按分类查询
	 * @throws SQLException 
	 * 
	 * */
	public PageBean<Book> findByCategory(String cid, int pc) throws SQLException {
		Map<String,Object> criteria=new HashMap<String,Object>();
		criteria.put("cid",cid);
		return findByCriteria(criteria, pc);
	}
	/**
     * 按作者查询
     * @throws SQLException
     */
	public PageBean<Book> findByAuthor(String author,int pc) throws SQLException{
		Map<String,Object> criteria= new HashMap<String,Object>();
		criteria.put("author", author);
		return findByCriteria(criteria,pc);
		
	}
	
	/**
	 * 按出版社查询 
	 * @param press
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	public PageBean<Book> findByPress(String press,int pc) throws SQLException{
	    Map<String,Object> criteria = new HashMap<String,Object>();
		criteria.put("press",press);
		return findByCriteria(criteria,pc);
	}
	/**
	 * 按图书名查询
	 * @param bname
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	public PageBean<Book> findByBname(String bname,int pc) throws SQLException{
		
		Map<String,Object> criteria = new HashMap<String,Object>();
		criteria.put("bname",bname);
		return findByCriteria(criteria,pc);
	}
	/**
	 * 组合查询（书名，作者，出版社）
	 * @param book
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	public PageBean<Book> findByCombination(Book book,int pc ) throws SQLException{
		Map<String,Object> criteria = new HashMap<String,Object>();
		criteria.put("bname",book.getBname());
		criteria.put("author",book.getAuthor());
		criteria.put("press",book.getPress());
		return findByCriteria(criteria,pc);
	}
	
	/**
	 * 按条件查询
	 * @param criteria
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	//根据条件分类查询
	private PageBean<Book> findByCriteria(Map<String, Object> criteria, int pc) throws SQLException {
		// TODO Auto-generated method stub
		//1.创建sql语句条件子句
		List<Object> params=new ArrayList<Object>();//条件，对应sql中的“？”
		StringBuilder whereSql=new StringBuilder(" where 1=1 ");
		for(String name:criteria.keySet()){
			//遍历每个循环条件
			Object value=criteria.get(name);
			if(value==null){
				//如果为空，说明没有这个条件
				continue;
			}
			//在程序中写sql语句时要注意空格
			whereSql.append(" and ").append(name).append(" like ?");
			params.add("%"+value+"%");	
		}
		//2.创建排序和limit子句
		String orderByAndLimitSql=" order by orderBy limit ? offset ?";
//		String orderByAndLimitSql=" order by orderBy Limit ?,?";   //mysql中的分页语句
		
		//3.生成个数查询语句，执行sql，得到记录数
		String cntSql="select count(*) from t_book " + whereSql;
		Number cnt=(Number) qr.query(cntSql, new ScalarHandler(),
				params.toArray());
		int tr = cnt !=null ? cnt.intValue() : 0;
		//4.生成记录查询，执行sql语句，得到当页记录
		String sql="select * from t_book"+whereSql + orderByAndLimitSql;
		
		//计算limit参数
		int ps=PageConstants.BOOK_PAGE_SIZE;//得到每页的记录数
		//以下两条语句是在mysql中的
	//	params.add(ps*(pc-1));//记录当前页第一条的数据的位置，从0开始
	//	params.add(ps);//一共查询到几条记录
		
		//在pgsql中 分页查询的语句如下：
		params.add(ps);//计算多少条记录
		params.add(ps*(pc-1));//忽略几条记录
		//把MapList映射成List<Book>
		/*
		 * 
		 *  SELECT TOP 页大小 * 
         FROM 
          (
           SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
          ) A
          WHERE RowNumber > 页大小*(页数-1)*/
		List<Map<String,Object>> mapList=qr.query(sql, new MapListHandler(),
				params.toArray());
		List<Book> bookList=new ArrayList<Book>();
		for(Map<String,Object> map:mapList){
			Book book=CommonUtils.toBean(map, Book.class);
			Category category=CommonUtils.toBean(map, Category.class);
			book.setCategory(category);
			bookList.add(book);
		}
		
		//5.创建PageBean,返回
		PageBean<Book> pb=new PageBean<Book>();
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		pb.setDataList(bookList);
		return pb;

	}
	
	
	/**
	 * 通过图书的bid加载图书
	 * 需要查询出图书所属的一级,二级分类
	 * @param bid
	 * @return
	 * @throws SQLException
	 */
	public Book load(String bid) throws SQLException{
		
		//多表查询
		String sql = "select * from t_book b,t_category c where b.cid=c.cid and bid=?";
		Map<String,Object> map = qr.query(sql, new MapHandler(), bid);
		//把结果映射成Book
		Book book = CommonUtils.toBean(map, Book.class);
		
		//把结果映射成Category，他是二级分类
		Category category = CommonUtils.toBean(map, Category.class);
		//创建父类
		Category parent = new Category();
	    //设置pid,即父类的id为其cid
	    parent.setCid((String)map.get("pid"));
		
		//设置Book对象,并返回
	    
	    category.setParent(parent);
	    book.setCategory(category);
		return book;
	}
	
	/**
	 * 加载分类下的所有的图书的个数
	 * @param cid
	 * @return
	 * @throws SQLException 
	 */
	public int findCountByCategory(String cid) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select count(1) from t_book where cid = ?";
	    Number count = (Number) qr.query(sql, new ScalarHandler(), cid);
		return count == null ? 0 : count.intValue();
	}
	
	/**
	 * 编辑图书
	 * @param book
	 * @throws SQLException 
	 */
	public void edit(Book book) throws SQLException {
		// TODO Auto-generated method stub
		String sql ="update t_book set bname=?,author=?,price=?,currPrice=?,discount=?,press=?, publishtime=?,pageNum=?,wordNum=?,printtime=?,bookSize=?,paper=?,cid=? where bid=?";
		Object[] params={book.getBname(),book.getAuthor(),book.getPrice(),book.getCurrPrice(),book.getDiscount(),
				book.getPress(),book.getPublishtime(),book.getPageNum(),book.getWordNum(),book.getPrinttime(),
				book.getBooksize(),book.getPaper(),book.getCategory().getCid(),book.getBid()
		};
		qr.update(sql, params);
	}
	
	/**
	 * 删除图书
	 * @param book
	 * @throws SQLException 
	 */
	public void delete(String bid) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "delete from t_book where bid = ?";
		qr.update(sql, bid);
	}
	/**
	 * 添加图书
	 * @param book
	 * @throws SQLException 
	 */
	public void add(Book book) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "insert into t_book where bid,bname,author,price,currPrice," +
				"discount,press,publishtime,edition,pageNum,wordNum,printtime," +
				"booksize,paper,cid,image_w,image_b values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		Object[] params = {book.getBid(),book.getBname(),book.getAuthor(),book.getPrice(),book.getCurrPrice(),
				book.getDiscount(),book.getPress(),book.getPublishtime(),book.getEdition(),book.getPageNum(),book.getWordNum(),
				book.getPrinttime(),book.getBooksize(),book.getPaper(),book.getCategory().getCid(),book.getImage_w(),book.getImage_b()		
		};
		qr.update(sql, params);
	}
  
}
